package org.rescue.search;

import java.sql.*;
import java.util.*;

import org.apache.commons.configuration.*;

import org.rescue.data.extraction.Annotation;
import org.rescue.data.extraction.AnnotationSet;


/**
 * 
 * DatasetDB is responsible for reading and writing datasets into 
 * persistant storage.  
 * 
 */
public class DatasetDB {

	String loginUser, loginPasswd, loginUrl;

	
    public DatasetDB() {   	
		try {		
			Configuration config = new PropertiesConfiguration("diamond.properties");			

			loginUser = config.getString("db.usr");
			loginPasswd = config.getString("db.pwd");
			loginUrl = config.getString("db.url");
		}
		catch (ConfigurationException ce) {
			System.out.println("Error opening diamond.properties configuration file:");
			System.out.println(ce.getMessage());			
		}
    }
    
	
    /**
     * getDatasetListing() is a utility method used by the web interface
     * to get summary information about datasets stored in the DB.
     */    
	public ArrayList getDatasetListing() {	

        PreparedStatement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
	    
        ArrayList<DatasetListing> listing = new ArrayList<DatasetListing>();

        
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(loginUrl,loginUser,loginPasswd);	
	        stmt = conn.prepareStatement("select dataset_id, query, size from dataset");
	        rs = stmt.executeQuery();
	        while (rs.next()) {
	        	DatasetListing dl = new DatasetListing(rs.getInt("dataset_id"), rs.getString("query"), rs.getInt("size") ); 
	        	listing.add(dl);
	        }

	        rs.close();
	        stmt.close();
	        conn.close();	        
	    }
		catch (SQLException ex) {
	        while (ex!=null){
	            System.err.println("SQLException: " + ex.getMessage());
	            System.err.println("SQLState: " + ex.getSQLState());
	            System.err.println("VendorError: " + ex.getErrorCode());
	            ex = ex.getNextException();
	        }
	    }
		catch (Exception ex) {
			System.err.println("Exception: " + ex.getMessage());
		}

		return listing;
    }
	
	
    public Dataset readDataset(int datasetId) {
   	
	    Dataset d = new Dataset();

	    CallableStatement cs1 = null, cs2 = null;
	    PreparedStatement stmt = null;
        ResultSet rs = null, rs2 = null;
        Connection conn = null;
	    
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(loginUrl,loginUser,loginPasswd);

			// first get the dataset metadata 
			stmt = conn.prepareStatement("select query from dataset where dataset_id = ? ");
	        stmt.setInt(1, datasetId);
	        rs = stmt.executeQuery();	                 
	        if (rs.next()) {
	        	String query = rs.getString("query");
	        	d.setQuery(query);
	        	
	        	// now get the all the documents
	        	stmt = conn.prepareStatement("select document_id, title, url, contents from document where dataset_id = ?");
	        	stmt.setInt(1,datasetId);
	        	rs = stmt.executeQuery();	        	
	        	while (rs.next()) {        		
	        		int id = rs.getInt("document_id");
	        		String url = rs.getString("url");
	        		String title = rs.getString("title");
	        		String contents = rs.getString("contents");
	        		
	        		Document doc = new Document(id, url, title, contents);
	        		d.addDocument(doc);	        		
	        	}
	        }
	        else {
	        	System.err.println("couldn't find the dataset");
	        }
	                
	        
	        // get the annotations for each of the documents
	        
	        for (Document doc: d.getDocuments()) {
	        	
	        	String sp1 = "{ CALL get_annotationsets(?,?) }";
	        	cs1 = conn.prepareCall(sp1);
	        	cs1.setInt(1, doc.getId());
	        	cs1.setInt(2, datasetId);
	        	cs1.execute();
	        	rs = cs1.getResultSet();	        	
	        	
	        	while (rs.next()){
	        		AnnotationSet as = new AnnotationSet(rs.getInt("taxonomy_id"));
	        		
	    			String sp2 = "{ CALL get_annotations(?,?) }";
	    			cs2 = conn.prepareCall(sp2);
	    			cs2.setInt(1, rs.getInt("annotationset_id"));
	    			cs2.setInt(2, datasetId);
	    			cs2.execute();
	        		rs2 = cs2.getResultSet();
	    			
		        	while (rs2.next()) {
		        		Annotation a = new Annotation(
		        				rs2.getString("label"),
		        				rs2.getInt("concept_id"), 
		        				rs2.getDouble("confidence"));
		        		as.addAnnotation(a);
		        	}
		        	
	        		doc.addAnnotationSet(as);
	        	}
	        	
	        }
	        

	        rs.close();
	        rs2.close();
	        stmt.close();
	        cs1.close();
	        cs2.close();
       
	        conn.close();
	        
	    }
		catch (SQLException ex) {
	        while (ex!=null){
	            System.err.println("SQLException: " + ex.getMessage());
	            System.err.println("SQLState: " + ex.getSQLState());
	            System.err.println("VendorError: " + ex.getErrorCode());
	            ex = ex.getNextException();
	        }
	    }
		catch (Exception ex) {
			System.err.println("Exception: " + ex.getMessage());
		}

		return d;
    }
	

    public Dataset writeDataset(Dataset d) {
       	
		Connection conn = null;
        PreparedStatement stmt = null, stmt3 = null;
        ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection(loginUrl,loginUser,loginPasswd);
	        conn.setAutoCommit(false);

	        
	        // create the new dataset record and keep track of its dataset_id
	        stmt = conn.prepareStatement("insert into dataset (query, size) values (?, ?)");
	        stmt.setString(1, d.getQuery());
	        stmt.setInt(2, d.getSize());
	        stmt.executeUpdate();
	        
	        int newDatasetKey = -1;
	        rs = stmt.getGeneratedKeys();	        
	        if (rs.next()) {
	        	newDatasetKey = rs.getInt(1);
	        }
	        else {
	        	System.err.println("couldn't get autoIncKey for dataset");
	        }
	        
	        
	        // create annotationset_ and annotation_ tables for this dataset
	        stmt = conn.prepareStatement("create table annotation_? (annotationset_id int(11) default NULL, concept_id int(11) default NULL, confidence double default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8");
	        stmt.setInt(1, newDatasetKey);
	        stmt.executeUpdate();	        
	        stmt = conn.prepareStatement("create table annotationset_? (annotationset_id int(11) NOT NULL auto_increment, document_id int(11) default NULL, taxonomy_id int(11) default NULL, PRIMARY KEY  (annotationset_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
	        stmt.setInt(1, newDatasetKey);
	        stmt.executeUpdate();        

	        
	 		// update dataset record with the annotation table info
	        String sqlstmt = "update dataset set annotationset_table='annotationset_" + newDatasetKey + "', annotation_table='annotation_" + newDatasetKey + "' where dataset_id = ?";
	        System.out.println(sqlstmt);
	        stmt = conn.prepareStatement(sqlstmt);
	        stmt.setInt(1, newDatasetKey);
	       	stmt.executeUpdate();                
	        
	       	
	        for(Document doc : d.getDocuments()) {        	        	
	        	// create the document record and keep track of its document_id
	        	stmt = conn.prepareStatement("insert into document (title, url, contents, dataset_id) values (?, ?, ?, ?)");
	        	stmt.setString(1, doc.getTitle());
	        	stmt.setString(2, doc.getURL());
	        	stmt.setString(3,doc.getContents());
	        	stmt.setInt(4, newDatasetKey);
	        	stmt.executeUpdate();

	        	int newDocKey = -1;
	        	rs = stmt.getGeneratedKeys();
	        	if (rs.next()) {
	        		newDocKey = rs.getInt(1);
	        	}
		        else {
		        	System.err.println("couldn't get key for document");
		        }
        	
        	
	        	// create the annotationset and annotation records
	        	int newAnnotationSetKey = -1;
	        	for(AnnotationSet as : doc.getAnnotationSets()) {

	        		String sp = "{ CALL create_annotationset(?,?,?) }";
	    			CallableStatement cs = conn.prepareCall(sp);
	    			cs.setInt(1, newDocKey);
	    			cs.setInt(2, as.getTaxonomyId());
	    			cs.setInt(3, newDatasetKey);
	    			cs.execute();
	        		rs = cs.getResultSet();
					if (rs.next()) {
						newAnnotationSetKey = rs.getInt(1);
					}	        		
	        			        		
	        		
					//System.out.println("there are " + as.getAnnotations().size() + " annotations.");
					for(Annotation a : as.getAnnotations()) {
	        			
						stmt3 = conn.prepareStatement("INSERT INTO annotation_" + newDatasetKey + " (annotationset_id, concept_id, confidence) " +
						  							  "VALUES (?, ?, ?)  ");
	        			stmt3.setInt(1, newAnnotationSetKey);
	        			stmt3.setInt(2, a.getId());
	        			stmt3.setDouble(3, a.getConfidence());
						stmt3.executeUpdate();
						
					}	        		
	        	}        	
	        }
	        
	        conn.commit();
	        conn.setAutoCommit(true);
	        
	        rs.close();
	        stmt.close();
	        conn.close();
	        
	    }
	
		catch (SQLException ex) {			
			try {
				System.err.println("rolling back transaction");
				conn.rollback();	
			}
			catch (SQLException e) {
	            System.err.println("SQLException: " + ex.getMessage());
			}
			
	        while (ex!=null){
	            System.err.println("SQLException: " + ex.getMessage());
	            System.err.println("SQLState: " + ex.getSQLState());
	            System.err.println("VendorError: " + ex.getErrorCode());
	            ex = ex.getNextException();
	        }
	    }
		catch (Exception ex) {
			System.err.println("Exception: " + ex.getMessage());
		}

		return d;
    }
    
    /**
     * Deletes all records from the database corresponding to the indicated
     * dataset.  This includes document records and associated annotation and
     * annotationset tables.
     * 
     * @param datasetId	the database id of the dataset to be deleted
     */
    public void deleteDataset(int datasetId) {
       	      	
        Connection conn = null;
    	    
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
	
			conn = DriverManager.getConnection(loginUrl,loginUser,loginPasswd);
	
			String sp = "{ CALL del_dataset(?) }";
			CallableStatement cs = conn.prepareCall(sp);
			cs.setInt(1, datasetId);
			cs.execute();
			
	        conn.close();        
	    }
	
		catch (SQLException ex) {
	        while (ex!=null){
	            System.err.println("SQLException: " + ex.getMessage());
	            System.err.println("SQLState: " + ex.getSQLState());
	            System.err.println("VendorError: " + ex.getErrorCode());
	            ex = ex.getNextException();
	        }
	    }
		catch (Exception ex) {
			System.err.println("Exception: " + ex.getMessage());
		}
    }


    
    
    

    
	/**
	 * @param args
	 */
	public static void main(String[] args) {

		
		// test read dataset from DB
		Integer datasetId = 1;
		DatasetDB datasetDB = new DatasetDB();
		Dataset testDataset = datasetDB.readDataset(datasetId);
		System.out.print(testDataset.toString());
	
		// test write dataset back to DB
		datasetDB.writeDataset(testDataset);
		
		
		
		// test get datasets listing
		//DatasetDB datasetDB = new DatasetDB();
		//System.out.print(datasetDB.getDatasetListing());
		
		
	}

}
